#include <QSqlQuery>
#include <QSqlResult>
#include <QSqlError>
#include <QVariant>

#include "database.h"
#include "server/user.h"
#include "common/status.h"
#include "common/friend.h"

Database::Database(const QString& dbName) : QSqlDatabase("QSQLITE") {
	setDatabaseName(dbName);
	createSchema();
}

Database::~Database() {
}

void Database::createSchema(){
	if(open()){
		QSqlQuery createSchema(*this);
		createSchema.exec("CREATE TABLE IF NOT EXISTS User(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,name VARCHAR NOT NULL UNIQUE,pass VARCHAR NOT NULL);");
		createSchema.exec("CREATE TABLE IF NOT EXISTS HasFriend(id1 INTEGER NOT NULL REFERENCES User ON DELETE CASCADE ON UPDATE CASCADE,id2 INTEGER NOT NULL REFERENCES User ON DELETE CASCADE ON UPDATE CASCADE,PRIMARY KEY (id1,id2));");
		createSchema.exec("CREATE TABLE IF NOT EXISTS InvitesFriend(id1 INTEGER NOT NULL REFERENCES User ON DELETE CASCADE ON UPDATE CASCADE,id2 INTEGER NOT NULL REFERENCES User ON DELETE CASCADE ON UPDATE CASCADE,PRIMARY KEY (id1,id2));");
		close();
	}
}

QList<Friend *> Database::all(){
	QList<Friend *> all;
	if(open()){
		QSqlQuery query(*this);
		query.prepare(
			"SELECT *\
			FROM User"
		);
		if(query.exec()){
			while(query.next()){
				QVariant id=query.value(0);
				QVariant name=query.value(1);
				all.push_back(new Friend(id.toInt(),Offline,name.toString()));
			}
		}
		close();
	}
	return all;
}

User* Database::getUser(const QString& userName,const QString& userPassword) {
	User *user=NULL;
	if(open()){
		QSqlQuery query(*this);
		query.prepare(
			"SELECT id,name\
			FROM User\
			WHERE User.name=:userName AND User.pass=:userPassword"
		);
		query.bindValue(":userName",QVariant(userName));
		query.bindValue(":userPassword",QVariant(userPassword));
		if(query.exec()){ //query.exec() returns a bool value
			if(query.next()){
				QVariant id=query.value(0);
				QVariant name=query.value(1);
				user=new User(id.toInt(),name.toString());
			}
		}
		close();
	}
	return user;
}

int Database::getUserId(const QString& userName) {
	int userId=-1;
	if(open()){
		QSqlQuery query(*this);
		query.prepare(
			"SELECT id\
			FROM User\
			WHERE User.name=:userName"
		);
		query.bindValue(":userName",QVariant(userName));
		if(query.exec()){ //query.exec() returns a bool value
			if(query.next()){
				QVariant id=query.value(0);
				userId = id.toInt();
			}
		}
		close();
	}
	return userId;
}

bool Database::setUser(const QString& userName,const QString& userPassword) {
	bool ret=false;
	if(open()){
		QSqlQuery query(*this);
		query.prepare(
			"INSERT INTO User(name,pass)\
			VALUES (:userName, :userPassword)"
		);
		query.bindValue(":userName",QVariant(userName));
		query.bindValue(":userPassword",QVariant(userPassword));
		ret=query.exec();
		close();
	}
	return ret;
}

QList<int> Database::getFriends(const QString& userName) {
	QList<int> friendsList;
	if(open()){
		QSqlQuery searchQuery(*this);
		searchQuery.prepare(
			"SELECT id2,U2.name\
			FROM User as U1,User as U2,HasFriend\
			WHERE	U1.name=:userName AND\
				U1.id=HasFriend.id1 AND\
				U2.id=HasFriend.id2"
		);
		searchQuery.bindValue(":userName",QVariant(userName));
		if(searchQuery.exec()){
			while(searchQuery.next()){
				QVariant friendId=searchQuery.value(0);
				friendsList.push_back(friendId.toInt());
			}
		}
		close();
	}
	return friendsList;
}

QList<int> Database::getFriends(int userId) {
	QList<int> friendsList;
	if(open()){
		QSqlQuery searchQuery(*this);
		searchQuery.prepare(
			"SELECT id2,U2.name\
			FROM User as U1,User as U2,HasFriend\
			WHERE	U1.id=:userId AND\
				U1.id=HasFriend.id1 AND\
				U2.id=HasFriend.id2"
		);
		searchQuery.bindValue(":userId",QVariant(userId));
		if(searchQuery.exec()){
			while(searchQuery.next()){
				QVariant friendId=searchQuery.value(0);
				friendsList.push_back(friendId.toInt());
			}
		}
		close();
	}
	return friendsList;
}

bool Database::inviteFriend(int id1, int id2){
	bool ret=false;
	if(open()){
		QSqlQuery insertQuery(*this);
		insertQuery.prepare(
			"INSERT INTO InvitesFriend(id1,id2)\
			VALUES (:id1, :id2)"
		);
		insertQuery.bindValue(":id1",id1);
		insertQuery.bindValue(":id2",id2);
		if(insertQuery.exec()){
			ret=true;
		}
		close();
	}
	return ret;
}

QList<int> Database::getInvitations(int userId){
	QList<int> invitationsList;
	if(open()){
		QSqlQuery searchQuery(*this);
		searchQuery.prepare(
			"SELECT id1\
			FROM 	InvitesFriend\
			WHERE	id2=:userId"
		);
		searchQuery.bindValue(":userId",QVariant(userId));
		if(searchQuery.exec()){
			while(searchQuery.next()){
				QVariant id=searchQuery.value(0);
				invitationsList.push_back(id.toInt());
			}
		}
		close();
	}
	return invitationsList;
}

bool Database::acceptInvitation(int id1,int id2) {
	bool ret=false;
	if(open()){
		QSqlQuery searchQuery(*this);
		searchQuery.prepare(
			"SELECT *\
			FROM InvitesFriend\
			WHERE id1=:id1 AND id2=:id2"
		);
		searchQuery.bindValue(":id1",QVariant(id1));
		searchQuery.bindValue(":id2",QVariant(id2));
		if(searchQuery.exec()){
			if(searchQuery.next()){
				QSqlQuery insertQuery(*this);
				insertQuery.prepare(
					"INSERT INTO HasFriend(id1,id2)\
					VALUES (:id1, :id2)"
				);
				insertQuery.bindValue(":id1",QVariant(id1));
				insertQuery.bindValue(":id2",QVariant(id2));
				if(insertQuery.exec()){
					insertQuery.prepare(
						"INSERT INTO HasFriend(id1,id2)\
						VALUES (:id2, :id1)"
					);
					insertQuery.bindValue(":id1",QVariant(id1));
					insertQuery.bindValue(":id2",QVariant(id2));
					if(insertQuery.exec()){
						QSqlQuery deleteQuery(*this);
						deleteQuery.prepare(
							"DELETE FROM InvitesFriend\
							WHERE id1=:id1 AND id2=:id2"
						);
						deleteQuery.bindValue(":id1",id1);
						deleteQuery.bindValue(":id2",id2);
						ret=deleteQuery.exec();
					}
				}
			}
		}
		close();
	}
	return ret;
}

bool Database::refuseInvitation(int id1,int id2){
	bool ret=false;
	if(open()){
		QSqlQuery searchQuery(*this);
		searchQuery.prepare(
			"SELECT *\
			FROM InvitesFriend\
			WHERE id1=:id1 AND id2=:id2"
		);
		searchQuery.bindValue(":id1",QVariant(id1));
		searchQuery.bindValue(":id2",QVariant(id2));
		if(searchQuery.exec()){
			if(searchQuery.next()){
				QSqlQuery deleteQuery(*this);
				deleteQuery.prepare(
					"DELETE FROM InvitesFriend\
					WHERE id1=:id1 AND id2=:id2"
				);
				deleteQuery.bindValue(":id1",id1);
				deleteQuery.bindValue(":id2",id2);
				ret=deleteQuery.exec();
			}
		}
		close();
	}
	return ret;
}

//bool Database::refuseInvitation(int id1,int id2){
//	bool ret=false;
//	if(open()){
//		QSqlQuery searchQuery(*this);
//		searchQuery.prepare(
//			"SELECT *\
//			FROM InvitesFriend\
//			WHERE id1=:id1 AND id2=:id2"
//		);
//		searchQuery.bindValue(":id1",QVariant(id1));
//		searchQuery.bindValue(":id2",QVariant(id2));
//		if(searchQuery.exec()){
//			if(searchQuery.next()){
//				ret = deleteInvitation(id1,id2);
//			}
//		}
//		close();
//	}
//	return ret;
//}

bool Database::deleteInvitation(int id1,int id2) {
	bool ret=false;
	if(open()){
		QSqlQuery deleteQuery(*this);
		deleteQuery.prepare(
			"DELETE FROM InvitesFriend\
			WHERE id1=:id1 AND id2=:id2"
		);
		deleteQuery.bindValue(":id1",id1);
		deleteQuery.bindValue(":id2",id2);
		ret=deleteQuery.exec();
		close();
	}
	return ret;
}

bool Database::setFriend(int id1, int id2) {
	bool ret=false;
	if(open()){
		QSqlQuery insertQuery(*this);
		insertQuery.prepare(
			"INSERT INTO HasFriend(id1,id2)\
			VALUES (:id1, :id2)"
		);
		insertQuery.bindValue(":id1",QVariant(id1));
		insertQuery.bindValue(":id2",QVariant(id2));
		if(insertQuery.exec()){
				insertQuery.prepare(
					"INSERT INTO HasFriend(id1,id2)\
					VALUES (:id2, :id1)"
				);
				insertQuery.bindValue(":id1",QVariant(id1));
				insertQuery.bindValue(":id2",QVariant(id2));
				ret=insertQuery.exec();
		}
		close();
	}
	return ret;
}

bool Database::setFriend(const QString& userName1,const QString& userName2) {
	bool ret=false;
	if(open()){
		QSqlQuery searchQuery(*this);
		searchQuery.prepare(
			"SELECT id\
			FROM User\
			WHERE User.name=:userName1 OR User.name=:userName2"
		);
		searchQuery.bindValue(":userName1",QVariant(userName1));
		searchQuery.bindValue(":userName2",QVariant(userName2));
		if(searchQuery.exec()){
			QVariant id1,id2;
			if(searchQuery.next()){
				id1=searchQuery.value(0);
			}
			if(searchQuery.next()){
				id2=searchQuery.value(0);
			}
			QSqlQuery insertQuery(*this);
			insertQuery.prepare(
				"INSERT INTO HasFriend(id1,id2)\
				VALUES (:id1, :id2)"
			);
			insertQuery.bindValue(":id1",id1);
			insertQuery.bindValue(":id2",id2);
			if(insertQuery.exec()){
					insertQuery.prepare(
						"INSERT INTO HasFriend(id1,id2)\
						VALUES (:id2, :id1)"
					);
					insertQuery.bindValue(":id1",id1);
					insertQuery.bindValue(":id2",id2);
					ret=insertQuery.exec();
			}
		}
		close();
	}
	return ret;
}

bool Database::deleteFriend(const QString& userName1,const QString& userName2) {
	bool ret=false;
	if(open()){
		QSqlQuery searchQuery(*this);
		searchQuery.prepare(
			"SELECT id\
			FROM User\
			WHERE User.name=:userName1 OR User.name=:userName2"
		);
		searchQuery.bindValue(":userName1",QVariant(userName1));
		searchQuery.bindValue(":userName2",QVariant(userName2));
		if(searchQuery.exec()){
			QVariant id1,id2;
			if(searchQuery.next()){
				id1=searchQuery.value(0);
			}
			if(searchQuery.next()){
				id2=searchQuery.value(0);
			}
			QSqlQuery deleteQuery(*this);
			deleteQuery.prepare(
				"DELETE FROM HasFriend\
				WHERE id1=:id1 AND id2=:id2"
			);
			deleteQuery.bindValue(":id1",id1);
			deleteQuery.bindValue(":id2",id2);
			if(deleteQuery.exec()) {
					deleteQuery.prepare(
						"DELETE FROM HasFriend\
						WHERE id1=:id2 AND id2=:id1"
					);
					deleteQuery.bindValue(":id1",id1);
					deleteQuery.bindValue(":id2",id2);
					ret=deleteQuery.exec();
			}
		}
		close();
	}
	return ret;
}

bool Database::setPassword(int id, const QString& oldPass,const QString& newPass) {
	bool ret=false;
	if(open()){
		QSqlQuery searchQuery(*this);
		searchQuery.prepare(
			"SELECT id\
			FROM User\
			WHERE User.id=:id AND User.pass=:oldPass"
		);
		searchQuery.bindValue(":id",QVariant(id));
		searchQuery.bindValue(":pass",QVariant(oldPass));
		if(searchQuery.exec()){
			if(searchQuery.next()){
				QSqlQuery updateQuery(*this);
				updateQuery.prepare(
					"UPDATE User SET pass=:newPass\
					WHERE id=:id"
				);
				updateQuery.bindValue(":newPass",QVariant(newPass));
				updateQuery.bindValue(":id",QVariant(id));
				ret=updateQuery.exec();
			}
		}
		close();
	}
	return ret;
}
